package com.teradata.bigdata.redis;

import cn.hutool.db.DbUtil;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.sql.SqlExecutor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @Project:
 * @Description:
 * @Version 1.0.0
 * @Throws SystemException:
 * @Author: <li>2019/11/26/026 Administrator Create 1.0
 * @Copyright ©2018-2019 al.github
 * @Modified By:
 */
public class TeradataUtil {

    private SimpleDataSource ds;
    private static final String url = "jdbc:teradata://10.220.8.60/DATABASE=nmart,CHARSET=ASCII,CLIENT_CHARSET=GBK";
    private static final String username = "tetl";
    private static final String password = "nmbi_tetl_lsc001";
    private static final String driverClass = "com.teradata.jdbc.TeraDriver";
    private static final String sql = "insert into nmart.tb_Tour_Scenic_Area(sceneryId,calDate,peopleNum,moveInNum,moveOutNum) values(?,?,?,?,?)";
    private static final String delSql = "delete from nmart.tb_Tour_Scenic_Area";


    public TeradataUtil() {
        init();
    }

    public Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public void init() {
        ds = new SimpleDataSource(url, username, password, driverClass);
    }

    public void batchInsert(List<TourScenicAreaVo> list) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (TourScenicAreaVo tourScenicAreaVo : list) {
                ps.setString(1, tourScenicAreaVo.getSceneryId());
                ps.setString(2, tourScenicAreaVo.getRealTimeCount().getCalDate().replace(",",""));
                ps.setInt(3, tourScenicAreaVo.getRealTimeCount().getPeopleNum());
                ps.setInt(4, tourScenicAreaVo.getRealTimeCount().getMoveInNum());
                ps.setInt(5, tourScenicAreaVo.getRealTimeCount().getMoveOutNum());
                ps.addBatch();
            }
            ps.executeBatch();
        } finally {
            DbUtil.close(ps);
        }
    }

    public void deleteRow() throws Exception{
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            SqlExecutor.execute(conn,delSql);
        } finally {
            DbUtil.close(ps);
        }
    }
}
